﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.Encodings.Web;
using System.Text.Json;
using System.Threading.Tasks;

//https://blog.csdn.net/zhxup606/article/details/148194391
namespace CodeGenerate.JsonGenerate
{
    // 配置记录类型（保持不变）
    public record JoinConfig(string MainTable, string MainAlias, string JoinTable, string JoinAlias, string OnClause);
    public record TableField
    {
        public string Name { get; init; }
        public string SqlType { get; init; }
        public string Description { get; init; }
        public bool IsEnum { get; init; }
        public int FieldType { get; init; }
        public int DataType { get; init; }
        public Dictionary<string, string>? EnumValues { get; set; }
    }
    public record TableConfig(string Name, string Alias, List<TableField> Fields);
    public record JsonConfig(
        string Id,
        string Name,
        string Sql,
        string SqlStatistics,
        string Orderby,
        List<string> EnumArray,
        List<FieldConfig> Fields,
        List<FieldConfig> AdvancedFilterFields,
        List<DataColumnConfig> DataColumns,
        List<TabConfig> Tabs);
    public record FieldConfig(string Table, string Field, string Header, int FieldType, int DataType);
    public record DataColumnConfig(
        string Table,
        string Field,
        string Header,
        int FieldType,
        int DataType,
        bool Require,
        bool DefaultHidden,
        bool Disabled,
        bool IsShow = true,
        Dictionary<string, object> ExtraInfo = null) : FieldConfig(Table, Field, Header, FieldType, DataType)
    {
        public DataColumnConfig(
            string Table,
            string Field,
            string Header,
            int FieldType,
            int DataType,
            bool Require,
            bool DefaultHidden,
            bool Disabled,
            bool? IsShow,
            Dictionary<string, object> ExtraInfo)
            : this(Table, Field, Header, FieldType, DataType, Require, DefaultHidden, Disabled, IsShow ?? true, ExtraInfo ?? new())
        {
        }
    }
    public record TabConfig(string DisplayName, List<string> GroupKey);
    public record AdditionalColumnConfig(
        string TableName,
        string TableAlias,
        string FieldName,
        string Alias,
        bool Require = true,
        bool DefaultHidden = false,
        bool Disabled = false,
        bool IsShow = true
    );

    // 配置类
    public class GeneratorConfig
    {
        public string Id { get; init; } = "material";
        public string Name { get; init; } = "物料管理";
        public List<string> EnumArray { get; init; } = [];
        public List<FieldConfig> Fields { get; init; } = [];
        public string MainTable { get; init; } = "";
        public string MainAlias { get; init; } = "";
        public HashSet<string> RequiredFields { get; init; } = [];
        public HashSet<string> HiddenFields { get; init; } = [];
        public List<TabConfig> Tabs { get; init; } = [];
        public Dictionary<string, int> EnumFields { get; init; } = new();
        public List<AdditionalColumnConfig> AdditionalColumns { get; init; } = [];
    }

    // 输入配置类（新增）
    public class InputConfig
    {
        public string ConnectionString { get; init; } = "";
        public List<JoinConfig> Joins { get; init; } = [];
        public GeneratorConfig GeneratorConfig { get; init; } = new();
    }

    // JsonConfigGenerator（保持不变）
    public class JsonConfigGenerator
    {
        private readonly string _connectionString;
        private readonly List<JoinConfig> _joins;
        private readonly GeneratorConfig _config;
        private List<TableConfig> _tables = [];
        private Dictionary<int, Dictionary<string, string>> _enumValuesCache = new();

        public JsonConfigGenerator(string connectionString, List<JoinConfig> joins, GeneratorConfig config = null)
        {
            _connectionString = connectionString;
            _joins = joins;
            _config = config ?? new GeneratorConfig();
        }

        public async Task<string> GenerateAsync()
        {
            _tables = await LoadTablesAsync();
            await LoadEnumValuesAsync();
            var config = new JsonConfig(
                Id: _config.Id,
                Name: _config.Name,
                Sql: GenerateSql(),
                SqlStatistics: GenerateSqlStatistics(),
                Orderby: "a.last_modify_date DESC",
                EnumArray: _config.EnumArray,
                Fields: _config.Fields,
                AdvancedFilterFields: GenerateAdvancedFilterFields(),
                DataColumns: GenerateDataColumns(),
                Tabs: _config.Tabs
            );
            var options = new JsonSerializerOptions
            {
                WriteIndented = true,
                Encoder = JavaScriptEncoder.UnsafeRelaxedJsonEscaping
            };
            return JsonSerializer.Serialize(config, options);
        }

        private async Task<List<TableConfig>> LoadTablesAsync()
        {
            var tables = new List<TableConfig>();
            var tableNames = _joins.Select(j => j.MainTable).Concat(_joins.Select(j => j.JoinTable)).Distinct();

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            foreach (var tableName in tableNames)
            {
                var (schema, name) = tableName.Contains(".") ? (tableName.Split('.')[0], tableName.Split('.').Last()) : ("dbo", tableName);
                var alias = _joins.FirstOrDefault(j => j.JoinTable == tableName)?.JoinAlias ?? _joins.FirstOrDefault(j => j.MainTable == tableName)?.MainAlias ?? "a";

                var fields = new List<TableField>();
                var query = @"
                SELECT 
                    c.COLUMN_NAME AS Name,
                    c.DATA_TYPE + CASE 
                        WHEN c.DATA_TYPE IN ('nvarchar', 'varchar', 'char', 'nchar') 
                        THEN '(' + ISNULL(CAST(c.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR), 'MAX') + ')'
                        ELSE '' END AS SqlType,
                    ISNULL(CAST(p.value AS NVARCHAR), c.COLUMN_NAME) AS Description
                FROM INFORMATION_SCHEMA.COLUMNS c
                LEFT JOIN sys.extended_properties p ON p.major_id = OBJECT_ID(@schema + '.' + @name) 
                    AND p.minor_id = COLUMNPROPERTY(OBJECT_ID(@schema + '.' + @name), c.COLUMN_NAME, 'ColumnId')
                    AND p.name = 'MS_Description'
                WHERE c.TABLE_SCHEMA = @schema AND c.TABLE_NAME = @name";

                using var command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@schema", schema);
                command.Parameters.AddWithValue("@name", name);

                using var reader = await command.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    var fieldName = reader.GetString(0);
                    var sqlType = reader.GetString(1);
                    var description = reader.GetString(2);
                    var isEnum = sqlType.StartsWith("tinyint", StringComparison.OrdinalIgnoreCase) || fieldName.EndsWith("_id", StringComparison.OrdinalIgnoreCase);
                    var fieldType = isEnum ? 3 : sqlType.StartsWith("datetime", StringComparison.OrdinalIgnoreCase) ? 2 : 0;
                    var dataType = _config.EnumFields.TryGetValue(fieldName, out var enumId) ? enumId : (isEnum ? 0 : 0);

                    var enumValues = isEnum && _config.EnumFields.ContainsKey(fieldName) && _enumValuesCache.TryGetValue(dataType, out var values)
                        ? values
                        : null;

                    var field = new TableField
                    {
                        Name = fieldName,
                        SqlType = sqlType,
                        Description = description,
                        IsEnum = isEnum,
                        FieldType = fieldType,
                        DataType = dataType,
                        EnumValues = enumValues
                    };

                    fields.Add(field);
                }

                if (fields.Any())
                    tables.Add(new(tableName, alias, fields));
            }

            return tables;
        }

        private async Task LoadEnumValuesAsync()
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            foreach (var (fieldName, enumId) in _config.EnumFields)
            {
                var query = "SELECT load_sql FROM tb_enum_info WHERE id = @id";
                using var command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@id", enumId);

                var loadSql = await command.ExecuteScalarAsync() as string;
                if (string.IsNullOrEmpty(loadSql))
                {
                    Console.WriteLine($"Warning: No load_sql found for enumId {enumId} ({fieldName}).");
                    _enumValuesCache[enumId] = new() { ["0"] = "未知" };
                    continue;
                }

                try
                {
                    using var enumCommand = new SqlCommand(loadSql, connection);
                    using var reader = await enumCommand.ExecuteReaderAsync();
                    var values = new Dictionary<string, string>();
                    while (await reader.ReadAsync())
                    {
                        var id = reader.GetValue(0)?.ToString();
                        var name = reader.GetString(1);
                        if (id != null)
                            values[id] = name;
                    }
                    _enumValuesCache[enumId] = values.Any() ? values : new() { ["0"] = "未知" };
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error executing load_sql for enumId {enumId} ({fieldName}): {ex.Message}");
                    _enumValuesCache[enumId] = new() { ["0"] = "未知" };
                }
            }
        }

        private string GetFieldDescription(string tableName, string fieldName)
        {
            var table = _tables.FirstOrDefault(t => t.Name == tableName);
            if (table == null)
            {
                Console.WriteLine($"Warning: Table '{tableName}' not found.");
                return fieldName;
            }
            var field = table.Fields.FirstOrDefault(f => f.Name == fieldName);
            if (field == null)
            {
                Console.WriteLine($"Warning: Field '{fieldName}' not found in table '{tableName}'.");
                return fieldName;
            }
            return field.Description;
        }

        private string GenerateSql() =>
            $"SELECT {string.Join(", ", GetSelectFields())} FROM {_config.MainTable} {_config.MainAlias} {string.Join(" ", _joins.Select(j => $"LEFT JOIN {j.JoinTable} {j.JoinAlias} ON {j.OnClause}"))} WHERE 1=1 {{@condition}}";

        private IEnumerable<string> GetSelectFields()
        {
            var main = _tables.FirstOrDefault(t => t.Name == _config.MainTable);
            if (main == null)
            {
                Console.WriteLine($"Warning: Table '{_config.MainTable}' not found.");
                yield break;
            }

            foreach (var f in main.Fields)
            {
                var alias = f.Name is "create_date" ? "createDate" : f.Name is "last_modify_date" ? "lastModifyDate" : f.Name;
                if (f.IsEnum)
                {
                    yield return $"CAST(a.{f.Name} AS NVARCHAR(100)) AS {f.Name}";
                    if (f.EnumValues != null && f.EnumValues.Any() && _config.EnumFields.ContainsKey(f.Name))
                    {
                        var caseWhen = $"CASE {string.Join(" ", f.EnumValues.Select(kv => $"WHEN a.{f.Name} = {kv.Key} THEN N'{kv.Value}'"))} END AS {f.Name}Name";
                        yield return caseWhen;
                    }
                }
                else
                {
                    yield return $"a.{f.Name} AS {alias}";
                }
            }

            foreach (var col in _config.AdditionalColumns)
            {
                yield return $"{col.TableAlias}.{col.FieldName} AS {col.Alias}";
            }
        }

        private string GenerateSqlStatistics() =>
            $"SELECT COUNT(0) AS total FROM {_config.MainTable} {_config.MainAlias} {string.Join(" ", _joins.Select(j => $"LEFT JOIN {j.JoinTable} {j.JoinAlias} ON {j.OnClause}"))} WHERE 1=1 {{@condition}}";

        private List<FieldConfig> GenerateAdvancedFilterFields()
        {
            var main = _tables.FirstOrDefault(t => t.Name == _config.MainTable);
            return main?.Fields.Select(f => new FieldConfig("a", f.Name, f.Description, f.FieldType, f.DataType)).ToList() ?? [];
        }

        private List<DataColumnConfig> GenerateDataColumns()
        {
            var columns = new List<DataColumnConfig>();
            var main = _tables.FirstOrDefault(t => t.Name == _config.MainTable);
            if (main == null) return columns;

            foreach (var f in main.Fields)
            {
                var alias = f.Name is "create_date" ? "createDate" : f.Name is "last_modify_date" ? "lastModifyDate" : f.Name;
                var isRequired = _config.RequiredFields.Contains(f.Name);
                var isHidden = _config.HiddenFields.Contains(f.Name);
                columns.Add(new("a", alias, f.Description, f.FieldType, f.DataType, isRequired, isHidden, isHidden, !isHidden && !f.IsEnum, new()));

                if (f.IsEnum && f.EnumValues != null && _config.EnumFields.ContainsKey(f.Name))
                {
                    columns.Add(new("a", $"{f.Name}Name", f.Description, 0, 0, isRequired, false, false, true, new()));
                }
            }

            columns.AddRange(_config.AdditionalColumns.Select(col =>
                new DataColumnConfig(
                    col.TableAlias,
                    col.Alias,
                    GetFieldDescription(col.TableName, col.FieldName) ?? col.FieldName,
                    0,
                    0,
                    col.Require,
                    col.DefaultHidden,
                    col.Disabled,
                    col.IsShow && !col.DefaultHidden,
                    new()
                )));

            return columns;
        }
    }

    // 测试方法
    public class JsonGenerateHelper
    {
        public static async Task Test(string inputJsonFile)
        {
            try
            {
                // 读取 JSON 配置文件
                if (!File.Exists(inputJsonFile))
                {
                    Console.WriteLine($"Error: Input JSON file '{inputJsonFile}' not found.");
                    return;
                }

                var jsonContent = await File.ReadAllTextAsync(inputJsonFile);
                var inputConfig = JsonSerializer.Deserialize<InputConfig>(jsonContent, new JsonSerializerOptions
                {
                    PropertyNameCaseInsensitive = true // 支持大小写不敏感
                });

                if (inputConfig == null)
                {
                    Console.WriteLine("Error: Failed to deserialize input JSON.");
                    return;
                }

                // 创建 JsonConfigGenerator
                var generator = new JsonConfigGenerator(
                    inputConfig.ConnectionString,
                    inputConfig.Joins,
                    inputConfig.GeneratorConfig
                );

                // 生成 JSON 配置
                var json = await generator.GenerateAsync();

                // 生成输出文件名（基于输入文件名）
                var outputFileName = Path.GetFileNameWithoutExtension(inputJsonFile).Replace("_input", "") + "_config.json";
                await File.WriteAllTextAsync(outputFileName, json);
                Console.WriteLine($"JSON config generated: {outputFileName}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }

        public static async Task Main()
        {
            // 示例：传入 JSON 文件路径
            await Test("material_input.json");
        }
    }
}

